﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using SharpDB.Entity;
using System.Timers;
using System.Text.RegularExpressions;
using System.Collections.Concurrent;
using System.Collections;
using System.IO;


namespace SharpDB.SqlServer
{
    public class DBInfo
    {
        /// <summary>
        /// 当前数据连接
        /// </summary>
        private SqlServerDB Db
        {
            get; set;
        }

        internal Timer timer = null;

        internal static IntervalMgr intervalMgr = null;

        private List<string> _TableNames = new List<string>();


        /// <summary>
        /// 缓存对象
        /// </summary>
        private static Dictionary<string,object> Cache = null;


        /// <summary>
        /// 内部缓存的过期依赖于表结构更改时间
        /// </summary>
        private Dictionary<string, DateTime> _Dict_TableStruct_Modify = new Dictionary<string, DateTime>();
        /// <summary>
        /// 表以及最后更新时间(只针对SqlServer/Oracle/MySql。MySql的所有表存储引擎必须为MyISAM。)
        /// </summary>
        public Dictionary<string, DateTime> Dict_TableStruct_Modify
        {
            get { return _Dict_TableStruct_Modify; }
        }


        public DBInfo(SqlServerDB db)
        {
            this.Db = db;
            Cache = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
            Reflesh();
            //开启轮询 查询数据库的表结构是否改变
            timer = new Timer();
            timer.Interval = 3 * 1000;            
            timer.Elapsed += Timer_Elapsed;
            intervalMgr = new IntervalMgr(timer, timer.Interval, 3);
            timer.Start();
        }
        

        /// <summary>
        /// 间隔时间段内查询 表结构是否有改变
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            var isChange = Reflesh();

            if (intervalMgr != null)
            {
                intervalMgr.InsertChangePoint(isChange);
            }
        }

        /// <summary>
        /// 即时刷新 获取最新表结构数据,返回是否 表结构是否有更新
        /// </summary>
        public bool Reflesh()
        {
            List<string> lstTabNames = new List<string>();
            Dictionary<string, DateTime> dict_New = new Dictionary<string, DateTime>();

            var resKV = GetTableStruct_Modify();
            dict_New = resKV.Key;

            this._TableNames = resKV.Value;
            this._Dict_TableStruct_Modify = dict_New;

            var arrExcept = dict_New.Except(Dict_TableStruct_Modify);
            var isChange = arrExcept.Any();

            if (isChange)
            {
                Cache["TableNames"] = _TableNames;

                foreach (var item in arrExcept)
                {
                    string tableName = item.Key;
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    List<string> lstColName = lstColInfo.Select(t => t.ColumnName).ToList();
                    Cache[tableName] = lstColName;
                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + "@" + tableName] = colInfo;
                    }
                }
            }
            return isChange;
        }

        /// <summary>
        /// 重置  查询表结构更改的 定时器 的 Interval 间隔
        /// </summary>
        public void ResetInterval()
        {
            if (intervalMgr != null)
            {
                intervalMgr.ReStart();
            }
        }

        /// <summary>
        /// 定时器 查询表结构的 间隔（单位：毫秒）
        /// </summary>
        public double TimerInterval
        {
            get
            {
                if (timer != null)
                    return timer.Interval;
                else
                    return 0;
            }
        }

        /// <summary>
        /// 返回：数据库所有表及对应的表结构修改历史 与 所有表名
        /// </summary>
        /// <returns></returns>
        private KeyValuePair<Dictionary<string,DateTime>,List<string>> GetTableStruct_Modify()
        {
            Dictionary<string, DateTime> dict_SD = new Dictionary<string, DateTime>();
            List<string> lstTabNames = new List<string>();
            string selSql = string.Empty;
            DataTable data = new DataTable("table");
            selSql = "select name,modify_date from sys.objects where type='U' and name <> 'dtproperties' and name <>'sysdiagrams'  order by modify_date desc";
            data = Db.QryTable(selSql);
            foreach (DataRow dr in data.Rows)
            {
                dict_SD.Add(dr["name"].ToString(), Convert.ToDateTime(dr["modify_date"]));
                lstTabNames.Add(dr["name"].ToString());
            }
            return new KeyValuePair<Dictionary<string, DateTime>, List<string>>(dict_SD, lstTabNames);
        }



        

        /// <summary>
        /// 获取当前数据库的所有表名
        /// </summary>
        public List<string> TableNames
        {
            get
            {
                if (!Cache.ContainsKey("TableNames"))
                {
                    Cache["TableNames"] = _TableNames;
                    return _TableNames;
                }
                return (Cache["TableNames"] as List<string>) ?? new List<string>();
            }
        }

        /// <summary>
        /// 根据表名获取所有列名
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <returns>所有列名</returns>
        public List<string> this[string tableName]
        {
            get
            {
                if (!Cache.ContainsKey(tableName))
                {
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    List<string> lstColName = lstColInfo.Select(t => t.ColumnName).ToList();

                    Cache[tableName] = lstColName;

                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + "@" + tableName] = colInfo;
                    }
                }
                return Cache[tableName] as List<string>;
            }
        }

        /// <summary>
        /// 根据表名、列名获取列信息
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public ColumnInfo this[string tableName, string columnName]
        {
            get
            {
                string key = columnName + "@" + tableName;
                if (!Cache.ContainsKey(key))
                {
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + "@" + tableName] = colInfo;
                    }
                }
                return Cache[key] as ColumnInfo;
            }
        }

        /// <summary>
        /// 当前数据库是否存在当前表
        /// </summary>
        /// <param name="table_Name">表名</param>
        /// <returns></returns>
        public bool IsExistTable(string table_Name)
        {
            return TableNames.Contains(table_Name, StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// 当前表是否存在当前列
        /// </summary>
        /// <param name="table_Name">表名</param>
        /// <param name="column_Name">列名</param>
        /// <returns></returns>
        public bool IsExistColumn(string table_Name,string column_Name)
        {
            return this[table_Name].Contains(column_Name, StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// 当前数据库所有的表名及对应所有的列信息
        /// </summary>
        public Dictionary<string, List<ColumnInfo>> GetAllTableColumnInfo()
        {
            Dictionary<string, List<ColumnInfo>> dict = new Dictionary<string, List<ColumnInfo>>(StringComparer.OrdinalIgnoreCase);
            foreach (string tabName in TableNames)
            {
                List<ColumnInfo> lstCol = GetAllColumnInfo(tabName);
                dict.Add(tabName, lstCol);
            }
            return dict;
        }

        /// <summary>
        /// 增加或更新 表 注释说明
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="comment">表说明</param>
        /// <returns></returns>
        public bool UpsertTableComment(string tableName, string comment)
        {
            string upsert_sql = string.Empty;
            bool result = false;
            upsert_sql = @" if exists (
                 SELECT case when a.colorder = 1 then d.name  else '' end as 表名,  case when a.colorder = 1 then isnull(f.value, '')  else '' end as 表说明
                FROM syscolumns a 
                       inner join sysobjects d 
                          on a.id = d.id 
                             and d.xtype = 'U' 
                             and d.name <> 'sys.extended_properties'
                       left join sys.extended_properties   f 
                         on a.id = f.major_id 
                            and f.minor_id = 0
                 where a.colorder = 1 and d.name<>'sysdiagrams'  and d.name='{0}' and f.value is not null
                 )
                 exec sp_updateextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL
                 else
                exec sp_addextendedproperty N'MS_Description', N'{1}', N'user', N'dbo', N'table', N'{0}', NULL, NULL";
            upsert_sql = string.Format(upsert_sql, tableName, comment);
            result = Db.ExecSql(upsert_sql) > 0;
            return result;
        }

        /// <summary>
        ///  增加或更新 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="comment">列说明</param>
        /// <returns></returns>
        public bool UpsertColumnComment(string tableName, string columnName, string comment)
        {
            string upsert_sql = string.Empty;
            bool result = false;
            upsert_sql = @"if exists (select * from   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) where objname = '{1}') EXEC sp_updateextendedproperty   'MS_Description','{2}','user',dbo,'table','{0}','column',{1} else EXEC sp_addextendedproperty @name=N'MS_Description' , @value=N'{2}' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'{0}', @level2type=N'COLUMN', @level2name=N'{1}' ";
            upsert_sql = string.Format(upsert_sql, tableName, columnName, comment);
            result = Db.ExecSql(upsert_sql) > 0;
            return result;
        }

        /// <summary>
        /// 获取 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public string GetColumnComment(string tableName, string columnName)
        {
            string result = string.Empty;
            string strSql = string.Empty;
            strSql = @"SELECT top 1 value FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" + tableName + "', 'column', default) where objname = '" + columnName + "'";
            result = Db.QrySingle<string>(strSql);
            return result;
        }

        /// <summary>
        /// 获取 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public Dictionary<string, string> GetColumnsComment(string tableName)
        {
            Dictionary<string, string> dict_result = new Dictionary<string, string>();
            string strSql = string.Empty;
            strSql = @"SELECT objname AS column_name,value AS comments FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" + tableName + "', 'column', default) ";
            dict_result = Db.QryTable(strSql).GetDict<string, string>("column_name", "comments");
            return dict_result;
        }

        /// <summary>
        /// 获取数据库所有表列注释
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetAllTableComment()
        {
            string strSql = string.Empty;
            Dictionary<string, string> dict = new Dictionary<string, string>();
            strSql = @"SELECT a.NAME,(SELECT TOP 1 value FROM sys.extended_properties b WHERE b.major_id=a.id and b.minor_id=0) AS value FROM sysobjects a
WHERE a.xtype='U' AND a.name <>'sysdiagrams' AND a.name <>'dtproperties' ORDER BY a.name asc";
            dict = Db.QryTable(strSql).GetDict<string, string>("name", "value");            
            return dict;
        }

        /// <summary>
        /// 获取数据库当前表的注释
        /// </summary>
        /// <returns></returns>
        public string GetTableComment(string tableName)
        {
            string result = string.Empty;
            string strSql = string.Empty;
            strSql = @"SELECT (SELECT TOP 1 value FROM sys.extended_properties b WHERE b.major_id=a.id and b.minor_id=0) AS value FROM sysobjects a
WHERE a.xtype='U' AND a.name <>'sysdiagrams' AND a.name <>'dtproperties' AND a.name='" + tableName + "' ";
            result = Db.QrySingle<string>(strSql);
            return result;
        }

        /// <summary>
        /// 获取当前表的所有列信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public List<ColumnInfo> GetAllColumnInfo(string tableName)
        {
            string strSql = string.Empty;
            List<ColumnInfo> lstCols = new List<ColumnInfo>();
            strSql = @"SELECT a.colorder Colorder,a.name ColumnName,b.name TypeName,(case when (SELECT count(*) FROM sysobjects  WHERE (name in (SELECT name FROM sysindexes  WHERE (id = a.id) AND (indid in  (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = 'PK'))>0 then 1 else null end) IsPK,(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else null end) IsIdentity,  CASE WHEN (charindex('int',b.name)>0) OR (charindex('time',b.name)>0) THEN NULL 
ELSE  
COLUMNPROPERTY(a.id,a.name,'PRECISION') 
--a.length
end as [Length], CASE WHEN ((charindex('int',b.name)>0) OR (charindex('time',b.name)>0)) THEN NULL ELSE isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),null) end as Scale,(case when a.isnullable=1 then 1 else null end) CanNull,isnull(e.text,'') DefaultVal,isnull(g.[value], ' ') AS DeText FROM  syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not NULL and d.name=N'" + tableName + "' order by a.id,a.colorder";
            lstCols = Db.QryTable(strSql).ConvertToListObject<ColumnInfo>();
            return lstCols;
        }


        /// <summary>
        /// 得到当前数据库所有视图名称
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllViews()
        {
            List<string> lstView = new List<string>();
            string strSql = "SELECT Table_Name as Name from information_schema.VIEWS";
            lstView = Db.QryTable(strSql).GetFirstCol<string>();
            return lstView;
        }


  



        /// <summary>
        /// 获取所有表、表描述、包含列的相关信息
        /// </summary>
        /// <returns></returns>
        public List<TableInfo> GetAllTableInfos()
        {
            List<TableInfo> lstTabInfo = new List<TableInfo>();
            Dictionary<string, string> dict_tabComment = GetAllTableComment();
            Dictionary<string, List<ColumnInfo>> dictColInfo = GetAllTableColumnInfo();

            foreach (var item in dict_tabComment)
            {
                TableInfo tabInfo = new TableInfo();
                tabInfo.TableName = item.Key;
                tabInfo.TabComment = item.Value;
                if (dictColInfo.ContainsKey(tabInfo.TableName))
                {
                    List<ColumnInfo> lstColInfo = dictColInfo[tabInfo.TableName];
                    tabInfo.LstColInfo = lstColInfo;
                    lstTabInfo.Add(tabInfo);
                }
                else
                {

                }
            }
            return lstTabInfo;
        }



        public bool DropTable(string tableName)
        {
            bool result = false;
            string drop_sql = string.Empty;
            drop_sql = "drop table " + tableName;
            Db.ExecSql(drop_sql);
            result = true;           
            return result;
        }

        public bool DropColumn(string tableName, string columnName)
        {
            bool result = false;
            string drop_sql = string.Empty;
            drop_sql = "alter table {0} drop column {1}";
            drop_sql = string.Format(drop_sql, tableName, columnName);
            Db.ExecSql(drop_sql);
            result = true;
            return result;
        }
    }
}

